﻿--------------dữ liệu bill award 2017
-----	Bảng chi tiết các khoản có dư nợ daily.dbo.DAILY_BIL_AWARD. Có 02 trường cần lưu ý là:
DECLARE @DAUKI DATE = (SELECT MIN(BUSINESS_DATE) FROM [SERVER12].[SME_SALE_CONTEST].[DBO].[BIL_AWARD_2017_RAW_DATA_DAILY])
DECLARE @CUOIKI DATE = (SELECT MAX(BUSINESS_DATE) FROM [SERVER12].[SME_SALE_CONTEST].[DBO].[BIL_AWARD_2017_RAW_DATA_DAILY])

delete from TBL_SALE_CONTEST_BIL_AWARD_RAW

 insert TBL_SALE_CONTEST_BIL_AWARD_RAW
SELECT CASE WHEN BUSINESS_DATE= @DAUKI THEN 'DAU KY'
			WHEN BUSINESS_DATE= @CUOIKI THEN 'CUOI KY' END AS CHECK_TIME,
 *
  FROM [SERVER12].[SME_SALE_CONTEST].[DBO].[BIL_AWARD_2017_RAW_DATA_DAILY] 
----+ XET_THUONG_MSBO_YN = CASE WHEN  QUICK_WIN = 'N' AND (NO_OVD_DAYS = 0 OR NO_OVD_DAYS IS NULL) AND DAO = DAO_CIF COLLATE DATABASE_DEFAULT  THEN 'Y' ELSE 'N' END : xem xét 1 LD có tính dư nợ cho DAO không 
----+  XET_THUONG_TTSME_YN = CASE WHEN  QUICK_WIN = 'N' AND (NO_OVD_DAYS = 0 OR NO_OVD_DAYS IS NULL ) THEN 'Y' ELSE 'N' END : Xem xét 1 LD có tính dư nợ cho trung tâm hay không

	-------	Bảng kết quả của MSBO: DAILY_BIL_AWARD_MSBO_RN. Trường XETTHUONG_YN = “y” khi thỏa mãn điều kiện dư nợ >= 3 tỷ

	delete from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY 
	
	  declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BIL_AWARD_RAW)

	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
	SELECT [DATA_DATE] AS BUSINESS_DATE
		  ,[BRANCH_CODE] AS BRANCH_CODE_SME
		  ,A.[DAO] 
		  ,[TÊN NHÂN VIÊN] AS DAO_NAME
		  ,isnull([DUNO_20170228],0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,'' as group_region
		  , 'MSBO' as type_rank
		  ,2
	  FROM [SERVER12].[SME_SALE_CONTEST].[DBO].[BIL_AWARD_2017_MSBO_RN_DAILY] A, TBL_HR B, TBL_BRANCH C
	  WHERE A.DAO= B.DAO AND B.BRANCH_CODE_SME = C.BRANCH_CODE_SME and a.[DATA_DATE] = @data_date
	  
	  update a
	  set a.group_region = b.vung_group
	  from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY a, (select vung_group, branch_code from [SERVER12].[SME_SALE_CONTEST].[dbo].[BIL_AWARD_2017_BRANCH_SME_RN_DAILY] group by vung_group, branch_code) b
	  where a.branch_code_sme = b.branch_code and a.type_rank = 'msbo'

-----	Bảng kết quả của Trung Tâm : DAILY_BIL_AWARD_BRANCH_SME_RN
----               Trường XETTHUONG_YN = “y” khi thỏa mãn điều kiện net dư nợ >= 20 tỷ
	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
SELECT [DATA_DATE] AS BUSINESS_DATE
		  ,[BRANCH_CODE] AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull([DUNO_20170228],0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,vung_group
		  , 'BRANCH' as type_rank
		  ,2
	  --INTO TBL_SALE_CONTEST_BILAWARD_BRANCH_RANKING_DAILY
from [SERVER12].[SME_SALE_CONTEST].[dbo].[BIL_AWARD_2017_BRANCH_SME_RN_DAILY] a,  TBL_BRANCH C
	  WHERE  a.BRANCH_CODE = C.BRANCH_CODE_SME and a.[DATA_DATE] = @data_date

-----	Bảng kết quả của Vùng : DAILY_BIL_AWARD_BRANCH_SME_RN
----                Trường XETTHUONG_YN = “y” khi thỏa mãn điều kiện net dư nợ >= 150 tỷ
	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
select @data_date AS BUSINESS_DATE
		  ,'' AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull([DUNO_20170228],0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,''
		  ,C.REGION
		  ,vung_group
		  , 'REGION' as type_rank
		  ,2
		  from [SERVER12].[SME_SALE_CONTEST].[dbo].[BIL_AWARD_2017_ZONE_SME_RN_DAILY] a,  (select distinct region from TBL_BRANCH) C
	  WHERE  a.ZONE_ID_SME = C.REGION and a.[DATA_DATE] = @data_date

	  ------------dữ liệu secured award 2017
	  --select * from TBL_SALE_CONTEST_PROGRAM
------	  •	Chương trình thế chấp: 
-------	RAW : [SECURED_AWARD_2017_RAW_DATA_DAILY]
-------	GIẢI MSBO : SECURED_AWARD_2017_MSBO_RN_DAILY
-------	GIẢI TRUNG TÂM : SECURED_AWARD_2017_BRANCH_RN_DAILY
-------	GIẢI VÙNG : SECURED_AWARD_2017_ZONE_RN_DAILY

	  --declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY)

	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
	   select @data_date AS BUSINESS_DATE
		  ,[BRANCH_CODE] AS BRANCH_CODE_SME
		  ,A.[DAO] 
		  ,[TÊN NHÂN VIÊN] AS DAO_NAME
		  ,isnull(DUNO_DAUKI,0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,'' as group_region
		  , 'MSBO' as type_rank
		  ,3
		   from [SERVER12].[SME_SALE_CONTEST].[dbo].SECURED_AWARD_2017_MSBO_RN_DAILY A, TBL_HR B, TBL_BRANCH C
	  WHERE A.DAO= B.DAO AND B.BRANCH_CODE_SME = C.BRANCH_CODE_SME

	  --declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY)

	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
SELECT @data_date AS BUSINESS_DATE
		  ,[BRANCH_CODE] AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull(DUNO_DAUKI,0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,vung_group
		  , 'BRANCH' as type_rank
		  ,3
		  from [SERVER12].[SME_SALE_CONTEST].[dbo].SECURED_AWARD_2017_BRANCH_RN_DAILY a,  TBL_BRANCH C
	  WHERE  a.BRANCH_CODE = C.BRANCH_CODE_SME

	  --declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY)
	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
select @data_date AS BUSINESS_DATE
		  ,'' AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull(DUNO_DAUKI,0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull([NET_DUNO],0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,''
		  ,C.REGION
		  ,vung_group
		  , 'REGION' as type_rank
		  ,3
		  from [SERVER12].[SME_SALE_CONTEST].[dbo].SECURED_AWARD_2017_ZONE_RN_DAILY a,  (select distinct region from TBL_BRANCH) C
	  WHERE  a.ZONE_ID_SME = C.REGION

	  ------------dữ liệu Auto award 2017
----------	  •	Chương trình ô tô: 
----------- RAW : [AUTO_AWARD_2017_RAW_DATA_DAILY]
----------- GIẢI MSBO : AUTO_AWARD_2017_MSBO_RN_DAILY
----------- GIẢI TRUNG TÂM : 
----------+ Tháng 3 : AUTO_AWARD_2017_BRANCH_RN_DAILY_201703 (ĐÃ CHỐT)
----------+ Tháng 4 : AUTO_AWARD_2017_BRANCH_RN_DAILY_201704 ( CÁI NÀY UPDATE TỪNG NGÀY)
-----------	GIẢI TRUNG TÂM TOÀN CHƯƠNG TRÌNH : AUTO_AWARD_2017_BRANCH_RN_DAILY
-----------	GIẢI VÙNG : AUTO_AWARD_2017_ZONE_RN_DAILY

	  
	  --declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY)
	  --select * from Gianglh3.daily.[dbo].AUTO_AWARD_2017_MSBO_RN_DAILY
	  --drop table #tam_auto_award_2017_msbo
	  select max(data_date) AS data_date, dao, count(acctno) as no_acct
	  into #tam_auto_award_2017_msbo 
	  from [SERVER12].[SME_SALE_CONTEST].[dbo].AUTO_AWARD_2017_MSBO_RN_DAILY group by dao

	  --select *,DENSE_RANK () OVER ( PARTITION BY a.Data_date ORDER BY no_acct desc) as XEP_HANG from #tam_auto_award_2017_msbo a, tbl_hr b
	  --where a.dao = b.DAO

	  --delete from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY where type_rank = 'msbo' and sale_contest_id =1
	  --SELECT * FROM #tam_auto_award_2017_msbo
	  --select * from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY where sale_contest_id = 1

	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
	  select
	   data_date
	  ,c.BRANCH_CODE_SME
	  , a.dao 
	  ,b.DAO_NAME
	  ,0
	  ,0
	  ,no_acct
	  ,'Y'
		  ,DENSE_RANK () OVER ( PARTITION BY region ORDER BY no_acct desc)
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,'' as group_region
		  , 'MSBO' as type_rank
		  ,1
	  from #tam_auto_award_2017_msbo a, TBL_HR B, TBL_BRANCH C
	  WHERE A.DAO= B.DAO AND B.BRANCH_CODE_SME = C.BRANCH_CODE_SME

	  --declare @data_date date = (select max(BUSINESS_DATE) from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY)
	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
	  SELECT @data_date AS BUSINESS_DATE
		  ,[BRANCH_CODE] AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull(DUNO_042017,0) AS DUNO_LAST_MONTH
		  ,isnull(DUNO_052017,0) as [DUNO_HIENTAI]
		  ,isnull(DOANHSO_GN_201705,0) as [NET_DUNO]
		  ,XETTHUONG_YN_052017
		  ,[XEP_HANG] 
		  ,C.BRANCH_NAME_SME
		  ,C.REGION
		  ,C.REGION
		  , 'BRANCH' as type_rank
		  ,1
		  from [SERVER12].[SME_SALE_CONTEST].[dbo].AUTO_AWARD_2017_BRANCH_RN_DAILY_201705 a,  TBL_BRANCH C
	  WHERE  a.BRANCH_CODE = C.BRANCH_CODE_SME

	  --select * from Gianglh3.daily.[dbo].[AUTO_AWARD_2017_BRANCH_RN_DAILY] order by vung_group, xep_hang

	  --select * from TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
	  Insert TBL_SALE_CONTEST_BILAWARD_RANKING_DAILY
select data_date AS BUSINESS_DATE
		  ,'' AS BRANCH_CODE_SME
		  ,''
		  ,''
		  ,isnull(DUNO_DAUKI,0) AS DUNO_LAST_MONTH
		  ,isnull([DUNO_HIENTAI],0) as [DUNO_HIENTAI]
		  ,isnull(DOANHSO_GN,0) as [NET_DUNO]
		  ,[XETTHUONG_YN]
		  ,[XEP_HANG] 
		  ,''
		  ,C.REGION
		  ,'ALL REGION'
		  , 'REGION' as type_rank
		  ,1
		  from [SERVER12].[SME_SALE_CONTEST].[dbo].AUTO_AWARD_2017_ZONE_RN_DAILY a,  (select distinct region from TBL_BRANCH) C
	  WHERE  a.ZONE_ID_SME = C.REGION
	  ----select Sale_contest_id
	  ----,Sale_contest_name
	  ----,Status
	  ----,category_id
	  ----,1 as has_data
	  ----, 1 as has_info
	  ----into TBL_SALE_CONTEST_PROGRAM
	  ----from TBL_SALE_CONTEST_PROGRAM_INFO

	  ----ALTER TABLE TBL_SALE_CONTEST_PROGRAM ADD SUMARY NVARCHAR(MAX)
	  --select * from TBL_PRODUCTHANDBOOK_CATEGORY
	  ----SP_RENAME 'TBL_SALE_CONTEST_PROGRAM', 'TBL_SALE_CONTEST_PROGRAM_INFO';
	  --select * from TBL_SALE_CONTEST_PROGRAM_INFO
update b
set b.date_data = a.BUSINESS_DATE
from (select max(business_date) as BUSINESS_DATE from TBL_SALE_CONTEST_BIL_AWARD_RAW) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_SALE_CONTEST_BIL_AWARD_RAW'
